﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using Business;

namespace Data
{

    public class DataPalco
    {
        public DataPalco()
        {

        }


        public List<String> getCategoriasPalco()
        {
            List<String> lc = new List<String>();
            Connect c = new Connect();
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT categoria FROM catPalco";
                SqlDataReader leitor = command.ExecuteReader();
                while (leitor.Read())
                {
                    if (!leitor.IsDBNull(0))
                        lc.Add(Convert.ToString(leitor[0]));
                }
                leitor.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }

            return lc;
        }

        public String getLoc(int idLoc)
        {
            Connect c = new Connect();
            String loc = "";
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT localidade FROM Localidade WHERE idLocalidade=" + idLoc;
                SqlDataReader leitor = command.ExecuteReader();
                if (leitor.Read())
                {
                    if(!leitor.IsDBNull(0))
                        loc = Convert.ToString(leitor[0]);
                }
                leitor.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return loc;
        }

        public int getIdLoc(String loc)
        {
            Connect c = new Connect();
            int idloc = -1;
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT idLocalidade FROM Localidade WHERE localidade='" + loc + "'";
                SqlDataReader leitor = command.ExecuteReader();
                if (leitor.Read())
                {
                    if(!leitor.IsDBNull(0))
                        idloc = Convert.ToInt16(leitor[0]);
                }
                leitor.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return idloc;
        }

        public String getCat(int idCat)
        {
            Connect c = new Connect();
            String cat = "";
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT categoria FROM catPalco WHERE idcatPalco=" + idCat;
                SqlDataReader leitor = command.ExecuteReader();
                if (leitor.Read())
                {
                    if(!leitor.IsDBNull(0))
                        cat = Convert.ToString(leitor[0]);
                }
                leitor.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return cat;
        }

        public int getIdCat(String cat)
        {
            Connect c = new Connect();
            int idcat = -1;
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT idcatPalco FROM catPalco WHERE categoria='" + cat + "'";
                SqlDataReader leitor = command.ExecuteReader();
                if (leitor.Read())
                {
                    if (!leitor.IsDBNull(0))
                        idcat = Convert.ToInt16(leitor[0]);
                }
                leitor.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return idcat;
        }

        public Palco getPalcoByID(int palcoID)
        {
            Connect c = new Connect();
            Palco p = new Palco();
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Palco WHERE idPalco=" + palcoID + " ";

                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    String nome = "";
                    String descricao = "";
                    String rua = "";
                    String codPostal = "";
                    double lat = -1;
                    double lon = -1;

                    int id = Convert.ToInt32(reader[0]);
                    if(!reader.IsDBNull(1))
                        nome = Convert.ToString(reader[1]);
                    if(!reader.IsDBNull(2))
                        descricao = Convert.ToString(reader[2]);
                    int idcat = Convert.ToInt16(reader[3]);
                    int idloc = Convert.ToInt16(reader[6]);

                    if(!reader.IsDBNull(4))
                        rua = Convert.ToString(reader[4]);
                    if(!reader.IsDBNull(5))
                        codPostal = Convert.ToString(reader[5]);
                    if (!reader.IsDBNull(7))
                        lat = Convert.ToDouble(reader[7]);
                    if(!reader.IsDBNull(8))
                        lon = Convert.ToDouble(reader[8]);
                    int status = Convert.ToInt32(reader[9]);
                    reader.Close();

                    cmd.CommandText = "SELECT categoria FROM catPalco WHERE idcatPalco=" + idcat + " ";
                    SqlDataReader leitor = cmd.ExecuteReader();
                    String categoria = "";
                    if (leitor.Read())
                        categoria = Convert.ToString(leitor[0]);
                    leitor.Close();
                    cmd.CommandText = "SELECT localidade FROM Localidade WHERE idLocalidade=" + idloc + " ";
                    leitor = cmd.ExecuteReader();
                    String localidade = "";
                    if (leitor.Read())
                        localidade = Convert.ToString(leitor[0]);

                    p = new Palco(nome, descricao, categoria, rua, codPostal, localidade, lat, lon);
                    p.setIDPalco(palcoID);
                    p.setStatus(status);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            return p;
        }


        public int insertPalco(Palco p)
        {
            int value = -1;
            
            Connect c = new Connect();
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();

                int loc = getIdLoc(p.getLocalidade());

                int cat = getIdCat(p.getCategoria());
                
                command.CommandText = "INSERT INTO Palco(nome,descricao,catPalco_idcatPalco,rua,codPostal,Localidade_idLocalidade,coordLat,coordLon,status) VALUES " + " ('" + p.getNomePalco() + "','" + p.getDescricaoPalco() + "'," + cat + ",'" + p.getRua() + "','" + p.getCodPostal() + "'," + loc + "," + p.getCoordLat().ToString().Replace(',', '.') + "," + p.getCoordLon().ToString().Replace(',', '.') + ",1)";

                int i = command.ExecuteNonQuery();

                command.CommandText = "SELECT MAX(idPalco) FROM Palco";
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                    value = Convert.ToInt16(reader[0]);
                reader.Close();
                conn.Close();

            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }

            return value;
        }




        public bool updatePalco(Palco p)
        {
            bool value = false;
            Connect c = new Connect();
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();

                int loc = getIdLoc(p.getLocalidade());

                int cat = getIdCat(p.getCategoria());

                command.CommandText = "UPDATE Palco SET nome='" + p.getNomePalco() + "',descricao='" + p.getDescricaoPalco() + "',catPalco_idcatPalco=" + cat + ",rua='" + p.getRua() + "',codPostal='" + p.getCodPostal() + "',Localidade_idLocalidade=" + loc + ",coordLat=" + p.getCoordLat().ToString().Replace(',', '.') + ",coordLon=" + p.getCoordLon().ToString().Replace(',', '.') + ",status=" + p.getStatus() + " WHERE (idPalco=" + p.getIDPalco() + ")";

                int i = command.ExecuteNonQuery();
                if (i > 0) value = true;
                conn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return value;
        }
 

        public bool setEstadoPalco(int palcoID, int novoEstado)
        {
            bool value = false;
            Connect c = new Connect();
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();

                command.CommandText = "UPDATE Palco SET status=" + novoEstado + " WHERE (idPalco=" + palcoID + ")";
                int i = command.ExecuteNonQuery();
                if (i > 0) value = true;
                conn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return value;
        }


        public ListaPalco getListaPalcos(int status)
        {
            Palco p = null;
            Connect c = new Connect();
            ListaPalco lp = new ListaPalco();
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();
                if (status == -10)
                    command.CommandText = "Select * from Palco";
                else
                    command.CommandText = "Select * from Palco where status=" + status;


                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    String nome = "";
                    String descricao = "";
                    String rua = "";
                    String codPostal = "";
                    double coordLat = -1;
                    double coordLon = -1;

                    String cat = getCat(Convert.ToInt16(reader[3]));
                   
                    String loc = getLoc(Convert.ToInt16(reader[6]));

                    if(!reader.IsDBNull(1))
                        nome = Convert.ToString(reader[1]);
                    if(!reader.IsDBNull(2))
                        descricao = Convert.ToString(reader[2]);
                    if(!reader.IsDBNull(4))
                        rua = Convert.ToString(reader[4]);
                    if (!reader.IsDBNull(5))
                        codPostal = Convert.ToString(reader[5]);
                    if(!reader.IsDBNull(7))
                        coordLat = Convert.ToDouble(reader[7]);
                    if(!reader.IsDBNull(8))
                        coordLon = Convert.ToDouble(reader[8]);

                    p = new Palco(nome,descricao,cat,rua,codPostal,loc,coordLat,coordLon);
                    p.setIDPalco(Convert.ToInt32(reader[0]));
                    p.setStatus(Convert.ToInt16(reader[9]));
                    lp.addPalco(p);
                }
                reader.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }

            return lp;
        }

        public ListaPalco getSearchListaPalcos(String name)
        {
            Palco p = null;
            Connect c = new Connect();
            ListaPalco lp = new ListaPalco();
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();

                command.CommandText = "Select * from Palco where nome LIKE '%" +name+"%'";
                
                

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    String nome = "";
                    String descricao = "";
                    String rua = "";
                    String codPostal = "";
                    double coordLat = -1;
                    double coordLon = -1;

                    String cat = getCat(Convert.ToInt16(reader[3]));

                    String loc = getLoc(Convert.ToInt16(reader[6]));

                    if (!reader.IsDBNull(1))
                        nome = Convert.ToString(reader[1]);
                    if (!reader.IsDBNull(2))
                        descricao = Convert.ToString(reader[2]);
                    if (!reader.IsDBNull(4))
                        rua = Convert.ToString(reader[4]);
                    if (!reader.IsDBNull(5))
                        codPostal = Convert.ToString(reader[5]);
                    if (!reader.IsDBNull(7))
                        coordLat = Convert.ToDouble(reader[7]);
                    if (!reader.IsDBNull(8))
                        coordLon = Convert.ToDouble(reader[8]);

                    p = new Palco(nome, descricao, cat, rua, codPostal, loc, coordLat, coordLon);
                    p.setIDPalco(Convert.ToInt32(reader[0]));
                    p.setStatus(Convert.ToInt16(reader[9]));
                    lp.addPalco(p);
                }
                reader.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }

            return lp;
        }

        public ListaPalco getPalcosEvento(int eventID)
        {
            Palco p = null;
            Connect c = new Connect();
            ListaPalco lp = new ListaPalco();
            List<int> lpi = new List<int>();
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT Palco_idPalco FROM EventoPalco WHERE Evento_idEvento=" + eventID;
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    lpi.Add(Convert.ToInt16(reader[0]));
                }
                reader.Close();
                foreach (int pi in lpi)
                {
                    command.CommandText = "Select * from Palco where idPalco=" + pi;
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        String nome = "cenassa";
                        String descricao = "";
                        String rua = "";
                        String codPostal = "";
                        double coordLat = -1;
                        double coordLon = -1;

                        String cat = getCat(Convert.ToInt16(reader[3]));

                        String loc = getLoc(Convert.ToInt16(reader[6]));

                        if (!reader.IsDBNull(1))
                            nome = Convert.ToString(reader[1]);
                        if (!reader.IsDBNull(2))
                            descricao = Convert.ToString(reader[2]);
                        if (!reader.IsDBNull(4))
                            rua = Convert.ToString(reader[4]);
                        if (!reader.IsDBNull(5))
                            codPostal = Convert.ToString(reader[5]);
                        if (!reader.IsDBNull(7))
                            coordLat = Convert.ToDouble(reader[7]);
                        if (!reader.IsDBNull(8))
                            coordLon = Convert.ToDouble(reader[8]);

                        p = new Palco(nome, descricao, cat, rua, codPostal, loc, coordLat, coordLon);
                        p.setIDPalco(Convert.ToInt32(reader[0]));
                        p.setStatus(Convert.ToInt16(reader[9]));
                        lp.addPalco(p);
                    }
                    reader.Close();
                }
                conn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return lp;
        }

    }

}
